unit UPersistenciaSQL;

interface

uses IBDatabase, IBSql, IBCustomDataSet, SysUtils, Dialogs, DB, IBQuery;

type
   TPersistenciaSQL = class

      private
         // aClasse :  TObject;
         conexao : TIBDatabase;
         transacao : TIBTransaction;

      public

         constructor Create;
         destructor Destroy; Overload;
         procedure criarConexao;
         function obterCodigo(pClasse : String) : Integer;
         // function carregarBaseDeDados(baseDeDados : String) : TDataSource;
         function carregarBaseDeDados(baseDeDados : String) : TIBDataSet;
         function executaUpdateQuery(sqlcmd : String) : Boolean;
         function executaQuery(sqlcmd : String) : TIBDataSet;

end;

implementation

uses UArquivoDeConfiguracao;

//
constructor TPersistenciaSQL.Create;
begin
  inherited Create;
end;

destructor TPersistenciaSQL.Destroy;
begin
    self.conexao.Destroy;
    self.transacao.Destroy;
  inherited Destroy;
end;

procedure TPersistenciaSQL.criarConexao;
begin
   try
      if (self.conexao = nil) then
         self.conexao := TIBDatabase.Create(nil);
      self.conexao.Close;
      self.conexao.Connected := false;
      self.conexao.DatabaseName := TArquivoDeConfiguracao.obterChave('Configuracao', 'BaseDeDados'); 
      self.conexao.LoginPrompt := false;
      self.conexao.Params.Clear;
      self.conexao.Params.Add('user_name=SYSDBA');
      self.conexao.Params.Add('password=qbpMR0M7');

      if (Self.transacao = nil) then
      begin
         Self.transacao := TIBTransaction.Create(nil);
         Self.transacao.DefaultDatabase := conexao;

         Self.transacao.Params.Add('read_committed');
         Self.transacao.Params.Add('rec_version');
         Self.transacao.Params.Add('nowait');
      end;

      Self.conexao.DefaultTransaction := transacao;
      Self.conexao.Connected := true;

   except

   end;

end;

function TPersistenciaSQL.obterCodigo(pClasse : String) : Integer;
var
  fetchDeDados : TIBDataSet;
begin
   try
      fetchDeDados := self.executaQuery('select max(id_contas' + pClasse + ' ) as codigo from contas' + pClasse);
      result := fetchDeDados.FieldByName('codigo').AsInteger + 1;
   except
      raise;
   end;
end;

function TPersistenciaSQL.carregarBaseDeDados(baseDeDados : String) : TIBDataSet;
var
   sqlcmd : String;

begin
   try
      criarConexao;
      if (baseDeDados = 'contasreceber') then
        sqlcmd := 'select * from ' + baseDeDados + ' where recebido = ''N''';
      if (baseDeDados = 'contaspagar') then
        sqlcmd := 'select * from ' + baseDeDados + ' where pago = ''N''';

      result := Self.executaQuery(sqlcmd);

   except
      on E:Exception do
      begin
         ShowMessage('Erro ao obter dados: ' + e.Message);
         result := nil;
      end;
   end;
end;

{
function TPersistenciaSQL.carregarBaseDeDados(baseDeDados : String) : TDataSource;
var
   sqlcmd : String;
   ibSQL : TIBSQL;
   dataSource : TDataSource;

begin

   try
      criarConexao;
      sqlcmd := 'select * from ' + baseDeDados;

      ibSQL := TIBSQL.Create(nil);
      ibSQL.Close;
      ibSQL.Database := Self.conexao;

      if not ibSQL.Transaction.InTransaction then
         ibSQL.Transaction.StartTransaction;

      ibSQL.SQL.Text := sqlcmd;
      ibSQL.ExecQuery;

      dataSource.DataSet := ibSQL.Database.DataSets[0];
      result := dataSource;

      ibSQL.Destroy;
      ibSQL.Free;

      ibSQL := nil;

   except
      on E:Exception do
      begin
         ShowMessage('Erro ao obter dados: ' + e.Message);
         ibSQL.Destroy;
         ibSQL := nil;
      end;
   end;
end;
}

function TPersistenciaSQL.executaUpdateQuery(sqlcmd : String) : Boolean;
var
   cmd : TIBSQL;
   a : Integer;

begin
   try
      if self.conexao = nil then
         criarConexao;

      cmd := TIBSQL.Create(nil);
      cmd.Close;
      cmd.Database := Self.conexao;
      cmd.Transaction := transacao;
      cmd.SQL.Text := sqlcmd;

      if not cmd.Transaction.InTransaction then
         cmd.Transaction.StartTransaction;

      cmd.ExecQuery;
      cmd.Transaction.Commit;

      a := cmd.RowsAffected;

      if (a <= 0) then
         result := false
      else
         result := true;

      cmd.Destroy;
      cmd := nil;

   except
      on E:Exception do
      begin
         ShowMessage('Erro ao atualizar dados: ' + e.Message + #13 + e.ClassName);
         cmd.Destroy;
         cmd := nil;
         result := false;
      end;
   end;
   
end;

function TPersistenciaSQL.executaQuery(sqlcmd : String) : TIBDataSet;
var
   cmd : TIBQuery;

begin
   try
      if conexao = nil then
         criarConexao;

      cmd := TIBQuery.Create(nil);
      cmd.Close;
      cmd.Database := conexao;
      cmd.Transaction := transacao;
      cmd.SQL.Text := sqlcmd;
      cmd.Open;

      result := TIBDataSet(cmd);

      // cmd.Destroy;
      // cmd := nil;

   except
      on e : Exception do
      begin
         ShowMessage(e.ClassName + ' - ' + e.Message);
         cmd.Destroy;
         cmd := nil;

         result := nil;
      end;
   end;

end;

end.

